#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    this->setWindowTitle("员工管理系统—sqlite_test");

    db = QSqlDatabase::addDatabase("QSQLITE");
    if(!db.isValid())
    {
        QMessageBox::critical(this,"错误提示","数据库驱动加载失败");
        throw std::runtime_error("数据库加载失败");
    }
    db.setDatabaseName("..\\..\\testDB.db");

    if(!db.open())
    {
        QMessageBox::critical(this,"错误提示","数据库打开失败");
        throw std::runtime_error(db.lastError().text().toStdString());
    }

    sqlModel = new QSqlQueryModel(this);
    loadModel = new QSqlQueryModel(this);
    loadModel->setQuery("SELECT * FROM employee");
    MainWindow::prepareSqlQuery();

    ui->tv_sqlite->setModel(this->sqlModel);
    ui->tv_sqlite->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tv_sqlite->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tv_sqlite->verticalHeader()->hide();

    connect(ui->cb_ID,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_name,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_ein,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_title,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_birythday,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_salary,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
    connect(ui->cb_notes,&QCheckBox::clicked,this,[this](){MainWindow::prepareSqlQuery();});
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::prepareSqlQuery()
{
    this->sqlQueryItem.clear();

    if(ui->cb_ID->isChecked())
        this->sqlQueryItem += ui->cb_ID->text() + ",";
    if(ui->cb_name->isChecked())
        this->sqlQueryItem += ui->cb_name->text() + ",";
    if(ui->cb_ein->isChecked())
        this->sqlQueryItem += ui->cb_ein->text() + ",";
    if(ui->cb_title->isChecked())
        this->sqlQueryItem += ui->cb_title->text() + ",";
    if(ui->cb_birythday->isChecked())
        this->sqlQueryItem += ui->cb_birythday->text() + ",";
    if(ui->cb_salary->isChecked())
        this->sqlQueryItem += ui->cb_salary->text() + ",";
    if(ui->cb_notes->isChecked())
        this->sqlQueryItem += ui->cb_notes->text() + ",";

    if(this->sqlQueryItem.endsWith(','))
        this->sqlQueryItem.back() = ' ';

    qDebug() << this->sqlQueryItem;
    sqlModel->setQuery(QString("SELECT %1 FROM employee").arg(this->sqlQueryItem));
    this->loadModel->setQuery("SELECT * FROM employee");
}

void MainWindow::on_tv_sqlite_clicked(const QModelIndex &index)
{
    ui->le_ID->setText(this->loadModel->data(this->loadModel->index(index.row(),0)).toString());
    ui->le_name->setText(this->loadModel->data(this->loadModel->index(index.row(),1)).toString());
    ui->le_ein->setText(this->loadModel->data(this->loadModel->index(index.row(),2)).toString());
    ui->le_title->setText(this->loadModel->data(this->loadModel->index(index.row(),3)).toString());
    ui->le_birythday->setText(this->loadModel->data(this->loadModel->index(index.row(),4)).toString());
    ui->le_salary->setText(this->loadModel->data(this->loadModel->index(index.row(),5)).toString());
    ui->te_notes->setText(this->loadModel->data(this->loadModel->index(index.row(),6)).toString());
}


void MainWindow::on_btn_insert_clicked()
{
    QSqlQuery sqlQuery;
    sqlQuery.prepare("INSERT INTO employee VALUES(?,?,?,?,?,?,?)");
    sqlQuery.addBindValue(ui->le_ID->text());
    sqlQuery.addBindValue(ui->le_name->text());
    sqlQuery.addBindValue(ui->le_ein->text());
    sqlQuery.addBindValue(ui->le_title->text());
    sqlQuery.addBindValue(ui->le_birythday->text());
    sqlQuery.addBindValue(ui->le_salary->text());
    sqlQuery.addBindValue(ui->te_notes->toPlainText());

    if(!sqlQuery.exec())
    {
        QMessageBox::critical(this,"插入信息错误提示","信息插入数据库语句执行失败:"+sqlQuery.lastError().text());
        return;
    }

    MainWindow::prepareSqlQuery();
}


void MainWindow::on_btn_delete_clicked()
{
    QItemSelectionModel* list = ui->tv_sqlite->selectionModel();
    if(!list->hasSelection())
    {
        QMessageBox::information(this,"操作提示","请先选择一条信息再删除");
        return;
    }

    QSqlQuery tmpq(QString("DELETE FROM employee WHERE id=%1")
                       .arg(this->sqlModel->data(this->sqlModel->index(list->currentIndex().row(),0)).toString()));

    qDebug() << this->sqlModel->data(this->sqlModel->index(list->currentIndex().row(),0)).toString();

    if(!tmpq.exec())
    {
        QMessageBox::information(this,"操作提示","删除信息失败" + tmpq.lastError().text());
    }

    MainWindow::prepareSqlQuery();
}


void MainWindow::on_btn_update_clicked()
{
    QSqlQuery sqlQuery;
    sqlQuery.prepare("UPDATE employee SET id=?,name=?,ein=?,title=?,birythday=?,salary=?,notes=? WHERE id = ?");
    sqlQuery.addBindValue(ui->le_ID->text());
    sqlQuery.addBindValue(ui->le_name->text());
    sqlQuery.addBindValue(ui->le_ein->text());
    sqlQuery.addBindValue(ui->le_title->text());
    sqlQuery.addBindValue(ui->le_birythday->text());
    sqlQuery.addBindValue(ui->le_salary->text());
    sqlQuery.addBindValue(ui->te_notes->toPlainText());
    QModelIndexList indexList = ui->tv_sqlite->selectionModel()->selectedRows();
    if(indexList.empty())
    {
        QMessageBox::critical(this,"修改信息提示","请先选中一条要修改的信息");
        return;
    }
    sqlQuery.addBindValue(this->loadModel->data(this->loadModel->index(indexList.first().row(),0)).toString());

    if(!sqlQuery.exec())
    {
        QMessageBox::critical(this,"修改信息执行sql语句提示","SQL语句执行失败:"+sqlQuery.lastError().text());
        return;
    }

    MainWindow::prepareSqlQuery();
}


void MainWindow::on_btn_find_clicked()
{
    QString sqlString = QString("SELECT %1 FROM employee WHERE name LIKE '%%2%'").arg(this->sqlQueryItem).arg(ui->le_name->text());
    this->sqlModel->setQuery(sqlString);
    this->loadModel->setQuery(sqlString);
}

